<?php

namespace app\modules\agent\controllers;
use app\models\AgentProfit;
use app\models\OrderRecharge;
use app\models\AgentBill;
use app\models\Agent;
use app\models\AgentAccount;
use app\models\AgentPackage;
use app\models\MoneyChange;
use app\models\UserCardRecharge;
use app\models\UserCardReserveRecharge;
use PhpOffice\PhpSpreadsheet\Cell\DataType;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use Exception;
use yii\web\ForbiddenHttpException;
use Yii;
use app\models\KeyMap;
use yii\data\Pagination;
class FinanceController extends BaseController{


    public function actionPackageprice()
    {

        $id = $this->get('id');
        $model = AgentPackage::findOne(['id' => $id])->toArray();
        return [
            'code'=>'1',
            'data'=>$model
        ];

    }
     public function actionUserCardRecharge(){
        $search_start_time = $this->get('search_start_time');
        $search_end_time = $this->get('search_end_time');
        $start_time = strtotime($this->get('search_start_time'));
        $end_time = strtotime($this->get('search_end_time'));
        $search_agent_id = $this->get('search_agent_id');
        $agent = $this->agent->id;
        $agent_id = Agent::find()->andWhere(['pid'=>$agent])->all();
        $searchAgent = Agent::find()
        ->andWhere(['agent.id' => $agent])
        ->one();
        $agentIdList = [$searchAgent->id];
        $agentIdList = array_merge($agentIdList, $searchAgent->getSubAidList());
        $query = UserCardRecharge::find()->alias('recharge');
        $query->joinWith('user user');
        $query->joinWith('agent agent');
        $query->joinWith('card card');
        $query->joinWith('packages packages');
        //$query->where(['recharge.agent_id' => $this->agent->id]);
        $query->andFilterWhere(['recharge.trade_no'=>$this->get('search_trade_no')]);
        $query->andFilterWhere(['like','card.iccid',$this->get('search_iccid')]);
        $query->andFilterWhere(['recharge.recharge_type'=>$this->get('search_recharge_type')]);
        if(empty($search_agent_id)){
            $query->andWhere(['recharge.agent_id'=>$agentIdList]);
        }else{
            $query->andFilterWhere(['recharge.agent_id'=>$search_agent_id]);
        }
        $query->andFilterWhere(['recharge.is_show'=>1]);
        if(!$search_start_time && !$search_end_time){
            $query->andFilterWhere(['recharge.pay_time'=>'']);
        }else{
            if(!$search_end_time){
                $time = strtotime(date('Y-m-d',time())) + 86399;
                $query->andFilterWhere(['between','recharge.pay_time',$start_time,$time]);
            }else{
                $search_end_times = $end_time + 86399;
                $query->andFilterWhere(['between','recharge.pay_time',$start_time,$search_end_times]);
            }
            
        }
        $pagination = new Pagination(['totalCount' => $query->count()]);
        $query->orderBy('recharge.id DESC');
        
        if ($this->get('export') == 'excel') { // 导出Excel文件
            try {
                $excel = new Spreadsheet();
                $sheet = $excel->setActiveSheetIndex(0);
                foreach ([
                             '编号',
                             '代理商',
                             'ICCID',
                             '套餐',
                             '时长(月)',
                             '成本价',
                             '销售价',
                             '交易编号',
                             '充值状态',
                             '支付时间',
                         ] as $index => $title) {
                    $sheet->setCellValue(chr(65 + $index) . '1', $title);
                }
                $sheet->getStyle('A1:Z1')->applyFromArray(['font' => ['bold' => true], 'alignment' => ['horizontal' => Alignment::HORIZONTAL_CENTER]]);
                $r = 2;
                /** @var Card $card */
                foreach ($query->each() as $recharge) {
                    $sheet->setCellValueExplicitByColumnAndRow(1, $r, $recharge->id, DataType::TYPE_NUMERIC);
                    $sheet->setCellValueExplicitByColumnAndRow(2, $r, $recharge->agent->username,DataType::TYPE_STRING);
                    $sheet->setCellValueExplicitByColumnAndRow(3, $r, $recharge->card->iccid,DataType::TYPE_STRING);
                    $sheet->setCellValueExplicitByColumnAndRow(4, $r, $recharge->package->name,DataType::TYPE_STRING);
                    $sheet->setCellValueExplicitByColumnAndRow(5, $r, $recharge->term_length,DataType::TYPE_STRING);
                    $sheet->setCellValueExplicitByColumnAndRow(6,$r, $recharge->price, DataType::TYPE_NUMERIC);
                    $sheet->setCellValueExplicitByColumnAndRow(7,$r, $recharge->money,DataType::TYPE_NUMERIC);
                    $sheet->setCellValueExplicitByColumnAndRow(8,$r, $recharge->trade_no,DataType::TYPE_STRING);
                    $sheet->setCellValueExplicitByColumnAndRow(9, $r, KeyMap::getValue('recharge_type', $recharge->recharge_type), DataType::TYPE_STRING);
                    $sheet->setCellValueExplicitByColumnAndRow(10,$r, Yii::$app->formatter->asDatetime($recharge->pay_time),DataType::TYPE_STRING);
                    $r++;
                }
                $sheet->freezePane('A2');

                header('Content-Type: application/vnd.ms-excel');
                header('Content-Disposition: attachment;filename="卡充值订单_' . date('Y-m-d') . '.xls"');
                header('Cache-Control: max-age=0');
                $excelWriter = IOFactory::createWriter($excel, 'Xls');
                $excelWriter->save('php://output');
            } catch (\Exception $e) {
                Yii::error('导出Excel文件时出错：' . $e->getMessage());
                Yii::error($e->getTraceAsString());
                echo $e->getMessage();
            }
            die();
        }
        if ($this->get('export') == 'csv') { // 导出CSV文件
            header('Content-Type: application/vnd.ms-excel;charset=UTF-8');
            header('Content-Disposition: attachment;filename="卡充值订单_' . date('Y-m-d') . '.csv"');
            header('Cache-Control: max-age=0');
            echo "编号,代理商,ICCID,套餐,时长(月),成本价,销售价,交易编号,充值状态,支付时间\r\n";
            /** @var Card $card */
            foreach ($query->each() as $recharge) {
                echo $recharge->id, ',';
                echo $recharge->agent->username, ',';
                echo "\t".$recharge->card->iccid, ','."\t";
                echo $recharge->package->name, ',';
                echo $recharge->term_length, ',';
                echo $recharge->price, ',';
                echo $recharge->money, ',';
                echo $recharge->trade_no, ',';
                echo KeyMap::getValue('recharge_type', $recharge->recharge_type), ',';
                echo Yii::$app->formatter->asDatetime($recharge->pay_time);
                echo "\r\n";
            }
            die();
        }
        $query->offset($pagination->offset)->limit($pagination->limit);
        $rechargeList = $query->all();

        return $this->render('user_card_recharge', [
            'search_start_time' =>  $search_start_time,
            'search_end_time' => $search_end_time,
            'rechargeList' => $rechargeList,
            'pagination' => $pagination,
            'agent' => $agent,
            'agent_id' => $agent_id
        ]);
    }
    // public function actionUserCardRecharge(){
    //  $query = AgentProfit::find()->alias('profit');
 //        $query->joinWith('agent agent');
 //        $query->joinWith('recharge recharge');
 //        $query->joinWith('package package');
 //        $query->joinWith('card card');
 //        $query->where(['profit.agent_id' => $this->agent->id]);
 //        $query->andFilterWhere(['recharge.trade_no' => $this->get('search_trade_no')]);
 //        $pagination = new Pagination(['totalCount' => $query->count()]);
 //        $query->orderBy('profit.id DESC');
 //        $query->offset($pagination->offset)->limit($pagination->limit);
 //        $profitList = $query->all();
 //        return $this->render('user_card_recharge', [
 //            'profitList' => $profitList,
 //            'pagination' => $pagination,
 //        ]);

    // }
    

    //代理商后台订购订单
    // public function actionOrderRecharge(){
    //     $query = OrderRecharge::find()->alias('order');
    //     $query->joinWith('package package');
    //     $query->where(['order.agent_id'=>$this->agent->id]);
    //     //$query->andFilterWhere(['order.agent_id'=>$this->agent_id]);
    //     $query->andFilterWhere(['order.order'=>$this->get('search_trade_no')]);
    //     $pagination = new Pagination(['totalCount' => $query->count()]);
    //     $query->orderBy('order.id DESC');
    //     $query->offset($pagination->offset)->limit($pagination->limit);
    //     $orderList = $query->all();
    //     return $this->render('order_recharge',[
    //         'orderList'  => $orderList,
    //         'pagination' => $pagination,
    //     ]);
    // }
    public function actionCommissionList(){
        $agent = Agent::findOne(['id'=>$this->agent->id]);
        $query = AgentBill::find();
        $query->andWhere(['agent_id'=>$agent->id]);
        $query->andWhere(['status'=>2]);
        $pagination = new Pagination(['totalCount' => $query->count()]);
        $query->orderBy('day DESC');
        $query->offset($pagination->offset)->limit($pagination->limit);
        $commissionList = $query->all();
        return $this->render('commission_list',[
            'agent' => $agent,
            'commissionList' => $commissionList,
            'pagination' => $pagination,
        ]);
    }
    //资金变动列表
    public function actionMoneyChange(){
        $search_start_time = $this->get('search_start_time');
        $search_end_time = $this->get('search_end_time');
        $start_time = strtotime($this->get('search_start_time'));
        $end_time = strtotime($this->get('search_end_time'));
        $query = MoneyChange::find();
        $query->andWhere(['agent_id'=>$this->agent->id]);
        $query->andFilterWhere(['order_no'=>$this->get('search_trade_no')]);
        $query->andFilterWhere(['money_source'=>$this->get('search_source')]);
        $query->orderBy('create_time DESC');
        if(!$search_start_time && !$search_end_time){
            $query->andFilterWhere(['create_time'=>'']);
        }else{
            if(!$search_end_time){
                $time = strtotime(date('Y-m-d',time())) + 86399;
                $query->andFilterWhere(['between','create_time',$start_time,$time]);
            }else{
                $search_end_times = $end_time + 86399;
                $query->andFilterWhere(['between','create_time',$start_time,$search_end_times]);
            }
            
        }
        $pagination = new Pagination(['totalCount' => $query->count()]);
        if ($this->get('export') == 'excel') { // 导出Excel文件
            try {
                $excel = new Spreadsheet();
                $sheet = $excel->setActiveSheetIndex(0);
                foreach ([
                             '编号',
                             '代理商',
                             '资金变化',
                             '余额',
                             '充值号码',
                             '订单号',
                             '资金来源',
                             '时间',
                         ] as $index => $title) {
                    $sheet->setCellValue(chr(65 + $index) . '1', $title);
                }
                $sheet->getStyle('A1:Z1')->applyFromArray(['font' => ['bold' => true], 'alignment' => ['horizontal' => Alignment::HORIZONTAL_CENTER]]);
                $r = 2;
                /** @var Card $card */
                foreach ($query->each() as $change) {
                    $sheet->setCellValueExplicitByColumnAndRow(1, $r, $change->id, DataType::TYPE_NUMERIC);
                    $sheet->setCellValueExplicitByColumnAndRow(2, $r, $change->agent->username,DataType::TYPE_STRING);
                    $sheet->setCellValueExplicitByColumnAndRow(3, $r, $change->money_change,DataType::TYPE_STRING);
                    $sheet->setCellValueExplicitByColumnAndRow(4, $r, $change->left_money,DataType::TYPE_STRING);
                    $sheet->setCellValueExplicitByColumnAndRow(5, $r, $change->recharge_number,DataType::TYPE_STRING);
                    $sheet->setCellValueExplicitByColumnAndRow(6,$r, $change->order_no, DataType::TYPE_STRING);
                    $sheet->setCellValueExplicitByColumnAndRow(7,$r, KeyMap::getValue('money_source', $change->money_source),DataType::TYPE_STRING);
                    $sheet->setCellValueExplicitByColumnAndRow(8,$r, Yii::$app->formatter->asDatetime($change->create_time),DataType::TYPE_STRING);
                    $r++;
                }
                $sheet->freezePane('A2');

                header('Content-Type: application/vnd.ms-excel');
                header('Content-Disposition: attachment;filename="资金变动_' . date('Y-m-d') . '.xls"');
                header('Cache-Control: max-age=0');
                $excelWriter = IOFactory::createWriter($excel, 'Xls');
                $excelWriter->save('php://output');
            } catch (\Exception $e) {
                Yii::error('导出Excel文件时出错：' . $e->getMessage());
                Yii::error($e->getTraceAsString());
                echo $e->getMessage();
            }
            die();
        }
        if ($this->get('export') == 'csv') { // 导出CSV文件
            header('Content-Type: application/vnd.ms-excel;charset=UTF-8');
            header('Content-Disposition: attachment;filename="卡充值订单_' . date('Y-m-d') . '.csv"');
            header('Cache-Control: max-age=0');
            echo "编号,代理商,资金变化,余额,充值号码,订单号,资金来源,时间\r\n";
            /** @var Card $card */
            foreach ($query->each() as $change) {
                echo $change->id, ',';
                echo $change->agent->username, ',';
                echo $change->money_change, ',';
                echo $change->left_money, ',';
                echo $change->recharge_number, ',';
                echo $change->order_no, ',';
                echo KeyMap::getValue('money_source', $change->money_source), ',';
                echo Yii::$app->formatter->asDatetime($change->create_time);
                echo "\r\n";
            }
            die();
        }
        $query->offset($pagination->offset)->limit($pagination->limit);
        $moneychangeList = $query->all();
        return $this->render('money_change',[
            'search_start_time' =>  $search_start_time,
            'search_end_time' => $search_end_time,
            'moneychangeList' => $moneychangeList,
            'pagination'      => $pagination,
        ]);
    }

    //卡预充值列表
    public function actionUserCardReserveRecharge(){
        $current_time = $this->get('search_time');
        $start_time = strtotime($current_time);
        $end_time = $start_time + 86399;
        $query = UserCardReserveRecharge::find()->alias('recharge');
        $query->andFilterWhere(['agent_id'=>$this->agent->id]);
        $query->andFilterWhere(['execute_status'=>$this->get('search_status')]);
        $query->andFilterWhere(['like','iccid',$this->get('search_iccid')]);
        if(!$current_time){
            $query->andFilterWhere(['recharge.create_time'=> '']);
        }else{
            $query->andFilterWhere(['between','recharge.create_time',$start_time,$end_time]);
        }
        $pagination = new Pagination(['totalCount' => $query->count()]);
        $query->orderBy('id DESC');
        $query->offset($pagination->offset)->limit($pagination->limit);
        $reserveList = $query->all();
        return $this->render('user_card_reserve_recharge_list',[
            'current_time'=> $current_time,
            'reserveList' => $reserveList,
            'pagination'  => $pagination,
        ]);
    }
}